package net.pessu.android.shoppoid.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import net.pessu.android.shoppoid.bean.Article;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class Database {

	private DatabaseHelper mOpenHelper;

	private static Database instance;

	Context wishList;

	SQLiteDatabase myDB = null;

	private static String TAG = "database";

	public static Database getInstance(Context wishList) {
		if (null == instance) {
			instance = new Database(wishList);
		}
		return instance;
	}

	public Database(Context pWishList) {
		this.wishList = pWishList;
		mOpenHelper = new DatabaseHelper(pWishList);

		if (getInfo("versionbase") == null) {
			// version 2
			try {
				mOpenHelper.getWritableDatabase().execSQL(
						"ALTER table " + ConstantesDatabase.MY_DATABASE_TABLE
								+ " add  prixorigine NUMERIC");
				mOpenHelper.getWritableDatabase().execSQL(
						"Update " + ConstantesDatabase.MY_DATABASE_TABLE
								+ " set prixorigine = prixreduit / txreduc;");
			} catch (Exception e) {
				Log.e(TAG, e.getMessage());
				addInfo("versionbase", "4");
			}
			addInfo("versionbase", "4");
		} else if (!getInfo("versionbase").equals("3")) {
			editInfo("versionbase", "4");
			mOpenHelper
					.getWritableDatabase()
					.execSQL(
							"Update "
									+ ConstantesDatabase.MY_DATABASE_TABLE
									+ " set prixorigine = prixreduit / txreduc where prixorigine = 0 or prixorigine is null;");
		}

	}

	public List<Article> retrieveArticles() {
		List<Article> listArticle = new ArrayList<Article>();
		Article article;
		myDB = mOpenHelper.getReadableDatabase();

		/* Create a Database. */
		try {
			/* retrieve data from database */
			Cursor c = myDB.rawQuery("SELECT * FROM "
					+ ConstantesDatabase.MY_DATABASE_TABLE, null);
			if (c != null) {

				int Column0 = c.getColumnIndex(ConstantesDatabase.ID);
				int Column1 = c.getColumnIndex(ConstantesDatabase.TITRE);
				int Column2 = c.getColumnIndex(ConstantesDatabase.DESC);
				int Column3 = c.getColumnIndex(ConstantesDatabase.PRIXREDUIT);
				int Column4 = c.getColumnIndex(ConstantesDatabase.CAT);
				int Column5 = c.getColumnIndex(ConstantesDatabase.PRIXORIGINE);

				// Check if our result was valid.
				c.moveToFirst();
				if (c.getCount() > 0) {
					// Loop through all Results
					do {
						article = new Article();
						article.setId(c.getInt(Column0));
						article.setTitre(c.getString(Column1));
						article.setDescription(c.getString(Column2));
						article.setTauxReduc(c.getDouble(Column3));
						article.setTypeAchat(c.getString(Column4));
						article.setPrixOrigine(c.getDouble(Column5));
						listArticle.add(article);
					} while (c.moveToNext());
				}
			}
		} catch (Exception e) {
			Log.e("Error", "Error", e);
		} finally {
			if (myDB != null)
				myDB.close();
		}
		return listArticle;
	}

	public Cursor retrieveArticlesAchetesC() {
		myDB = mOpenHelper.getReadableDatabase();
		Cursor c = null;
		/* Create a Database. */
		try {
			/* retrieve data from database */
			c = myDB.rawQuery("SELECT " + ConstantesDatabase.TITRE + ", "
					+ ConstantesDatabase.PRIXREDUIT + " FROM "
					+ ConstantesDatabase.MY_DATABASE_TABLE
					+ " where isAcheter=1", null);

		} catch (Exception e) {
			Log.e("Error", "Error", e);
		} finally {
			if (myDB != null)
				myDB.close();
		}
		return c;
	}

	public List<Article> retrieveArticlesAchetes() {
		List<Article> listArticle = new ArrayList<Article>();
		Article article;
		myDB = mOpenHelper.getReadableDatabase();
		/* Create a Database. */
		try {
			/* retrieve data from database */
			Cursor c = myDB.rawQuery("SELECT * FROM "
					+ ConstantesDatabase.MY_DATABASE_TABLE
					+ " where isAcheter=1", null);
			if (c != null) {

				int Column0 = c.getColumnIndex(ConstantesDatabase.ID);
				int Column1 = c.getColumnIndex(ConstantesDatabase.TITRE);
				int Column2 = c.getColumnIndex(ConstantesDatabase.DESC);
				int Column3 = c.getColumnIndex(ConstantesDatabase.TXREDUC);
				int Column4 = c.getColumnIndex(ConstantesDatabase.PRIXREDUIT);

				// Check if our result was valid.
				c.moveToFirst();
				if (c.getCount() > 0) {
					// Loop through all Results
					do {
						article = new Article();
						article.setId(c.getInt(Column0));
						article.setTitre(c.getString(Column1));
						article.setDescription(c.getString(Column2));
						article.setTauxReduc(c.getDouble(Column3));
						article.setPrixreduit(c.getDouble(Column4));
						listArticle.add(article);
					} while (c.moveToNext());
				}
			}
		} catch (Exception e) {
			Log.e("Error", "Error", e);
		} finally {
			if (myDB != null)
				myDB.close();
		}
		return listArticle;
	}

	public Article retrieveArticleById(int id) {
		Article article = null;
		myDB = mOpenHelper.getReadableDatabase();
		/* Create a Database. */
		try {
			String[] columns = { ConstantesDatabase.ID,
					ConstantesDatabase.TITRE, ConstantesDatabase.DESC,
					ConstantesDatabase.PRIXREDUIT, ConstantesDatabase.TXREDUC,
					ConstantesDatabase.CAT, ConstantesDatabase.DATEARTICLE,
					ConstantesDatabase.ISACHETER,
					ConstantesDatabase.PRIXORIGINE };

			/* retrieve data from database */
			Cursor c = myDB.query(ConstantesDatabase.MY_DATABASE_TABLE,
					columns, ConstantesDatabase.ID + "=" + id, null, null,
					null, null);
			if (c != null) {

				int Column0 = c.getColumnIndex(ConstantesDatabase.ID);
				int Column1 = c.getColumnIndex(ConstantesDatabase.TITRE);
				int Column2 = c.getColumnIndex(ConstantesDatabase.DESC);
				int Column3 = c.getColumnIndex(ConstantesDatabase.PRIXREDUIT);
				int Column4 = c.getColumnIndex(ConstantesDatabase.TXREDUC);
				int Column5 = c.getColumnIndex(ConstantesDatabase.CAT);
				int Column6 = c.getColumnIndex(ConstantesDatabase.DATEARTICLE);
				int Column7 = c.getColumnIndex(ConstantesDatabase.ISACHETER);
				int Column8 = c.getColumnIndex(ConstantesDatabase.PRIXORIGINE);

				// Check if our result was valid.
				c.moveToFirst();
				if (c.getCount() > 0) {
					// Loop through all Results
					do {
						article = new Article();
						article.setId(c.getInt(Column0));
						article.setTitre(c.getString(Column1));
						article.setDescription(c.getString(Column2));
						article.setPrixreduit(c.getDouble(Column3));
						article.setTauxReduc(c.getDouble(Column4));
						article.setTypeAchat(c.getString(Column5));
						article.setDate(new Date(c.getLong(Column6)));
						article.setAcheter((c.getInt(Column7) == 1) ? true
								: false);
						article.setPrixOrigine(c.getDouble(Column8));
					} while (c.moveToNext());
				}
			}
		} catch (Exception e) {
			Log.e("Error", "Error", e);
		} finally {
			if (myDB != null)
				myDB.close();
		}
		return article;
	}

	public boolean addArticles(Article article) {
		boolean done = false;
		myDB = mOpenHelper.getWritableDatabase();
		/* Create a Database. */
		try {
			ContentValues mapValues = new ContentValues();
			mapValues.put(ConstantesDatabase.TITRE, article.getTitre().trim());
			mapValues.put(ConstantesDatabase.DESC, article.getDescription()
					.trim());
			mapValues.put(ConstantesDatabase.PRIXREDUIT,
					article.getPrixreduit());
			mapValues.put(ConstantesDatabase.TXREDUC, article.getTauxReduc());
			mapValues.put(ConstantesDatabase.CAT, article.getTypeAchat());
			mapValues.put(ConstantesDatabase.DATEARTICLE, article.getDate()
					.getTime());
			mapValues.put(ConstantesDatabase.ISACHETER, article.isAcheter() ? 1
					: 0);
			mapValues.put(ConstantesDatabase.PRIXORIGINE,
					article.getPrixOrigine());

			myDB.insert(ConstantesDatabase.MY_DATABASE_TABLE, null, mapValues);

			done = true;
		} catch (Exception e) {
			Log.e("Error", "Error", e);
		} finally {
			if (myDB != null)
				myDB.close();
		}
		return done;
	}

	public boolean editArticles(Article article) {
		boolean done = false;
		myDB = mOpenHelper.getWritableDatabase();
		/* Create a Database. */
		try {
			ContentValues mapValues = new ContentValues();
			mapValues.put(ConstantesDatabase.TITRE, article.getTitre().trim());
			mapValues.put(ConstantesDatabase.DESC, article.getDescription()
					.trim());
			mapValues.put(ConstantesDatabase.PRIXREDUIT,
					article.getPrixreduit());
			mapValues.put(ConstantesDatabase.PRIXORIGINE,
					article.getPrixOrigine());
			mapValues.put(ConstantesDatabase.TXREDUC, article.getTauxReduc());
			mapValues.put(ConstantesDatabase.CAT, article.getTypeAchat());
			mapValues.put(ConstantesDatabase.DATEARTICLE, article.getDate()
					.getTime());
			mapValues.put(ConstantesDatabase.ISACHETER, article.isAcheter() ? 1
					: 0);

			myDB.update(ConstantesDatabase.MY_DATABASE_TABLE, mapValues,
					"id = " + article.getId(), null);
			done = true;
		} catch (Exception e) {
			Log.e("Error", "Error", e);
		} finally {
			if (myDB != null)
				myDB.close();
		}
		return done;
	}

	public boolean acheterArticles(int id) {
		boolean done = false;
		myDB = mOpenHelper.getWritableDatabase();
		/* Create a Database. */
		try {
			ContentValues mapValues = new ContentValues();
			mapValues.put(ConstantesDatabase.ISACHETER, 1);

			myDB.update(ConstantesDatabase.MY_DATABASE_TABLE, mapValues,
					"id = " + id, null);

			done = true;
		} catch (Exception e) {
			Log.e("Error", "Error", e);
		} finally {
			if (myDB != null)
				myDB.close();
		}
		return done;
	}

	public boolean unacheterArticles(int id) {
		boolean done = false;
		myDB = mOpenHelper.getWritableDatabase();
		/* Create a Database. */
		try {
			ContentValues mapValues = new ContentValues();
			mapValues.put(ConstantesDatabase.ISACHETER, 0);

			myDB.update(ConstantesDatabase.MY_DATABASE_TABLE, mapValues,
					"id = " + id, null);

			done = true;
		} catch (Exception e) {
			Log.e("Error", "Error", e);
		} finally {
			if (myDB != null)
				myDB.close();
		}
		return done;
	}

	public void deleteArticle(int id) {
		try {
			myDB = mOpenHelper.getWritableDatabase();
			myDB.delete(ConstantesDatabase.MY_DATABASE_TABLE, "id = " + id,
					null);

		} catch (Exception e) {
			Log.e("Error", "Error", e);
		} finally {
			if (myDB != null)
				myDB.close();
		}

	}

	public void deleteArticles() {
		try {
			myDB = mOpenHelper.getWritableDatabase();
			myDB.execSQL("DELETE FROM " + ConstantesDatabase.MY_DATABASE_TABLE);

		} catch (Exception e) {
			Log.e("Error", "Error", e);
		} finally {
			if (myDB != null)
				myDB.close();
		}
	}

	public boolean addInfo(String key, String value) {
		boolean done = false;
		myDB = mOpenHelper.getWritableDatabase();
		/* Create a Database. */
		try {
			ContentValues mapValues = new ContentValues();
			mapValues.put(ConstantesDatabase.CLE, key);
			mapValues.put(ConstantesDatabase.VALUE, value);

			myDB.insert(ConstantesDatabase.MY_DATABASE_TABLE2, null, mapValues);

			done = true;
		} catch (Exception e) {
			Log.e("Error", "Error", e);
		} finally {
			if (myDB != null)
				myDB.close();
		}
		return done;
	}

	public boolean editInfo(String key, String value) {
		boolean done = false;
		myDB = mOpenHelper.getWritableDatabase();
		/* Create a Database. */
		try {
			ContentValues mapValues = new ContentValues();
			mapValues.put(ConstantesDatabase.CLE, key);
			mapValues.put(ConstantesDatabase.VALUE, value);

			myDB.update(ConstantesDatabase.MY_DATABASE_TABLE2, mapValues,
					"cle = '" + key + "' ", null);
			done = true;
		} catch (Exception e) {
			Log.e("Error", "Error", e);
		} finally {
			if (myDB != null)
				myDB.close();
		}
		return done;
	}

	public String getInfo(String key) {
		String retour = null;
		myDB = mOpenHelper.getReadableDatabase();

		try {

			Cursor c = myDB.query(ConstantesDatabase.MY_DATABASE_TABLE2,
					new String[] { ConstantesDatabase.VALUE },
					ConstantesDatabase.CLE + " = '" + key + "' ", null, null,
					null, null);
			if (c != null) {

				int Column0 = c.getColumnIndex(ConstantesDatabase.VALUE);

				// Check if our result was valid.
				c.moveToFirst();
				if (c.getCount() > 0) {
					retour = c.getString(Column0);
				}
			}
		} catch (Exception e) {
			Log.e("Error", "Error", e);
		} finally {
			if (myDB != null)
				myDB.close();
		}
		return retour;
	}

}
